﻿using System;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Windows.Forms;
using System.Data;
using System.Collections.ObjectModel;
using System.Globalization;

namespace University.Workshop.DataAccess
{
    public class RoomDataAccess : IDisposable
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=DataBaseHotel;Integrated Security=true";
        private SqlConnection cnn;
        public Collection<Room> GetAllRoomByIdType(int idType)
        {
            //Room[] RoomList = null;
            Collection<Room> RoomList = new Collection<Room>();
            //ArraySegment<Room> RoomList;
            //ado.net
            cnn = new SqlConnection(ConnString);

            string sqlSentence = "SELECT * from room WHERE idType = @id";

            SqlCommand cmd = cnn.CreateCommand();

            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Parameters.AddWithValue("@id", idType);
            try
            {
                cnn.Open();
                //cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return RoomList;
                }
                int i = 0;

                while (reader.Read())
                {
                    int idRoom = (int)reader["idRoom"];
                    String name = reader["Name"].ToString();
                    int Type = (int)reader["idType"];
                    RoomList.Add(new Room(idRoom, name, Type));
                    i++;
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {

                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }

            return RoomList;
        }
        public decimal GetPriceByName(String name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select * from [room] where name = @Name";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", name);

            try
            {
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return 0;
                }
                reader.Read();
                return (decimal)reader["price"];
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public int GetTypeOfRoomByName(String name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select * from [room] where name = @Name";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", name);

            try
            {
                cnn.Open();
                cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return 0;
                }
                reader.Read();
                return (int)reader["idType"];
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public int GetByName(String name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select idType from [type] where name = @Name";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", name);

            try
            {
                cnn.Open();
                cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return 0;
                }
                reader.Read();
                return (int)reader["idType"];
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public Boolean ExistingRoom(Room room)
        {
            if (room == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            try
            {
                cnn = new SqlConnection(ConnString);
                string sqlSentence = "SELECT * FROM [room] WHERE [Name] =";
                sqlSentence += "@name";

                SqlCommand cmd = cnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.AddWithValue("@name", room.Name);
                cnn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                System.Console.WriteLine(cmd.ToString());
                if (reader.HasRows)
                {
                    return true;
                }
                else { return false; }
            }
            catch (Exception)
            {
                throw new ArgumentException("Error creating a room");
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void NewRoom(Room room)
        {
            if (room == null)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"INSERT INTO [DataBaseHotel].[dbo].[room]([name],[idType])VALUES (@name,@idType)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@name", room.Name);
            cmd.Parameters.AddWithValue("@idType", room.Types);

            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException("Error creating a user", e);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void GetAllRoomsName(DataGridView rooms, int idType)
        {
            if (rooms == null)
                throw new ArgumentException("Invalid Input Parameters");

            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();

            cmd.CommandText = "SELECT  dbo.room.Name AS RoomName FROM [DataBaseHotel].[dbo].[room]";
            if (idType != 0)
            {
                cmd.CommandText = "SELECT  dbo.room.Name AS RoomName FROM [DataBaseHotel].[dbo].[room] Where idType = @id";
                cmd.Parameters.AddWithValue("@id", idType);
            }
            //cmd.CommandText = "SELECT idRoom, dbo.room.Name AS RoomName, dbo.Type.Name as TypeName FROM dbo.room INNER JOIN dbo.Type ON dbo.room.idType =dbo.Type.idType ";
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    rooms.Rows.Add();
                    rooms["Rooms", index].Value = reader["RoomName"].ToString();
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
        }
        public void GetAllRooms(DataGridView rooms)
        {
            if (rooms == null)
                throw new ArgumentException("Invalid Input Parameters");

            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();

            //cmd.CommandText = "SELECT * FROM [DataBaseHotel].[dbo].[Administrator]";
            cmd.CommandText = "SELECT idRoom, dbo.room.Name AS RoomName, dbo.Type.Name as TypeName FROM dbo.room INNER JOIN dbo.Type ON dbo.room.idType =dbo.Type.idType ";
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    rooms.Rows.Add();
                    rooms["idRoom", index].Value = reader["idRoom"].ToString();
                    rooms["RoomName", index].Value = reader["RoomName"].ToString();
                    rooms["Type", index].Value = reader["TypeName"].ToString();
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
        }
        public int GetIdRoomByName(String name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select idRoom from [room] where name = @Name";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@Name", name);

            try
            {
                cnn.Open();
                cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return 0;
                }
                reader.Read();
                return (int)reader["idRoom"];
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void DrawStates(DateTime dateTime, DataGridView roomsDataGrid, int idType, int lapTime)
        {
            BookingDataAccess bookingDal = new BookingDataAccess();
            bookingDal.Dispose();
            CheckInDataAccess checkInDal = new CheckInDataAccess();
            checkInDal.Dispose();
            if (roomsDataGrid == null) { throw new ArgumentException("Invalid Input Parameters"); }
            if (dateTime == null) { throw new ArgumentException("Invalid Input Parameters"); }
            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();

            //cmd.CommandText = "SELECT * FROM [DataBaseHotel].[dbo].[Administrator]";
            cmd.CommandText = "SELECT idRoom FROM dbo.room ";
            if (idType != 0)
            {
                cmd.CommandText = "SELECT idRoom FROM dbo.room Where idType = @id";
                cmd.Parameters.AddWithValue("@id", idType);
            }
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    for (int iterator = 0; iterator < lapTime; iterator++)
                    {
                        if (checkInDal.IsToken((int)reader["idRoom"], dateTime.AddDays(iterator)))
                        {
                            roomsDataGrid[dateTime.AddDays(iterator).ToShortDateString().Substring(0, 5), index].Value = "Taken";
                            //roomsDataGrid[dateTime.AddDays(iterator).ToShortDateString().Substring(0, 5), index].Style.ForeColor = Color.Red;
                        }
                        else
                        {
                            if (bookingDal.IsBooked((int)reader["idRoom"], dateTime.AddDays(iterator)))
                            {
                                roomsDataGrid[dateTime.AddDays(iterator).ToShortDateString().Substring(0, 5), index].Value = "Booked";
                            }
                            else
                            {
                                //Avialable
                                roomsDataGrid[dateTime.AddDays(iterator).ToShortDateString().Substring(0, 5), index].Value = "Free";
                            }
                        }
                    }
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
                //throw new ArgumentException("Problem");
            }
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
        }

        public void GetTakenRooms(DateTime dateTime, DataGridView rooms)
        {
            if (rooms == null)
                throw new ArgumentException("invalid Input Parameters");
            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "SELECT r.Name AS RoomName,Fullname AS Client,Price AS Costo FROM TokenRooms tr,room r,Client c,[Type] t WHERE (CheckIn<@Date OR CheckIn=@Date) AND (CheckOut>@Date OR CheckOut=@Date) AND r.idRoom=tr.idRoom  AND c.ClientId=tr.ClientId  AND t.idType=r.idType";
            cmd.Parameters.AddWithValue("@Date", dateTime);
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    rooms.Rows.Add();
                    rooms["Habitacion", index].Value = reader["RoomName"].ToString();
                    rooms["Cliente", index].Value = reader["Client"].ToString();
                    rooms["Costo", index].Value = reader["Costo"].ToString();
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }

        }

        public void GetTakenRoomsByName(DateTime dateTime, DataGridView rooms, string roomName)
        {
            if (rooms == null)
                throw new ArgumentException("invalid Input Parameters");
            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = @"SELECT r.[Name] AS RoomName
                                        ,[ClientCI]
                                        ,[CheckIn] 
                                        ,[Price]  
                                    FROM [DataBaseHotel].[dbo].[TokenRooms] tr, [DataBaseHotel].[dbo].[room] r,[DataBaseHotel].[dbo].[Type] t 
                                    WHERE r.[idType] = t.[idType] and tr.[idroom]=r.[idRoom] and tr.[ClientCI] like @search+'%' ";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@search", roomName);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    rooms.Rows.Add();
                    DateTime oldDate = Convert.ToDateTime(reader["CheckIn"].ToString(),CultureInfo.InvariantCulture);
                    TimeSpan ts = dateTime - oldDate;
                    rooms["Room", index].Value = reader["RoomName"].ToString();
                    rooms["Client", index].Value = reader["ClientCI"].ToString();
                    decimal total = ts.Days;
                    total = total * Convert.ToDecimal(reader["Price"].ToString(),CultureInfo.InvariantCulture);
                    rooms["Price", index].Value = total;
                    rooms["UsedDays", index].Value = ts.Days;
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
        }
            
        public float GetRoomPrice(int idRoom)
        {
            cnn = new SqlConnection(ConnString);
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = "SELECT Price AS Costo FROM room r,[Type] t WHERE r.idRoom=@Id";
            cmd.Parameters.AddWithValue("@Id", idRoom);
            cmd.CommandType = CommandType.Text;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            SqlDataReader reader = cmd.ExecuteReader();
        
            try
            {
                return (float)reader["Costo"];
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }

        }
        public string GetTypeById(int id)
        {
            if (id == 0)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select Name from [type] where idType = @id";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@id", id);

            try
            {
                cnn.Open();
                cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    return reader["Name"].ToString();
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading DataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return null;
        }

        public string GetRoomNameById(int roomId)
        {
            if (roomId==0)
            {
                throw new ArgumentException("Invalid Input Parameters");
            }
            //ado.net
            cnn = new SqlConnection(ConnString);
            string sqlSentence = "select * from [room] where idRoom = @id";

            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = System.Data.CommandType.Text;
         
            cmd.Parameters.AddWithValue("@id", roomId);

            try
            {
                cnn.Open();
                cnn.ChangeDatabase("DataBaseHotel");
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                reader.Read();
                return reader["Name"].ToString();
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error reading dataBase", e);
            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void InsertPayRoom(string idClient, string price)
        {
            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"INSERT INTO [DataBaseHotel].[dbo].[PayRooms] ([IdClient],[Date],[price])VALUES (@name,@date,@price)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@name", idClient);
            cmd.Parameters.AddWithValue("@date", DateTime.Today);
            cmd.Parameters.AddWithValue("@price", price);

            try
            {
                cnn.Open();
                cmd.ExecuteNonQuery();
             
            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void DeleteCheckIn(string idClient)
        {
            cnn = new SqlConnection(ConnString);
            string sqlSentence = @"DELETE FROM [DataBaseHotel].[dbo].[TokenRooms] WHERE [ClientCI]=@name";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@name", idClient);
            try
            {
                cnn.Open();
                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message);

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public void GetRoomInCheckIn(ComboBox room) 
        {
            if (room == null)
                throw new ArgumentException("Invalid Input Parameters");

            cnn = new SqlConnection(ConnString);
            SqlCommand command = cnn.CreateCommand();
            command.CommandText = "SELECT [Name] FROM [DataBaseHotel].[dbo].[TokenRooms] t, [DataBaseHotel].[dbo].[room] r WHERE r.[idRoom]=t.[idRoom]";
            command.CommandType = CommandType.Text;
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }
            
            try
            {
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    room.Items.Add(reader["Name"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (command.Connection.State == ConnectionState.Open)
            {
                command.Connection.Close();
            }
        }
        #region Miembros de IDisposable

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (cnn != null)
                {
                    cnn.Dispose();
                    cnn = null;
                }
            }
        }

        #endregion



        
    }
}
